package com.liusy.code;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.liusy.code.bean.ColumnInfo;

/**
 * 数据库操作类
 * @author liusy
 *
 */
@SuppressWarnings("restriction")
public class DbHelp
{
	
	   public static String DATE_FORMAT      = "yyyy-MM-dd";
	   public static String TIME_FORMAT      = "HH:mm:ss";
	   public static String TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss";

	   public static String ID_COLUMN        = "id";
	   public static String COUNT_COLUMN     = "cnt";
	   
	   
	private Connection conn=null;
	
	private static DbHelp dbDbHelp = null;
	
	private String user = "";
	
	private DbHelp(String driver,String url,String user,String pwd) throws SQLException, ClassNotFoundException
	{
		this.initConn(driver, url, user, pwd);
	}
	
	public static synchronized DbHelp  getDbHelp(String driver,String url,String user,String pwd) throws SQLException, ClassNotFoundException
	{
		if(dbDbHelp == null)
		{
			dbDbHelp = new DbHelp(driver, url, user, pwd);
		}
		return dbDbHelp;
	}
	
   
     private Connection initConn(String driver,String url,String user,String pwd) throws SQLException, ClassNotFoundException
     {
    		if(conn==null)
    		{
    				 	this.user = user;
    			        Class.forName(driver);
    			        Properties props =new Properties();
    			        props.put("remarksReporting","true");
    			        props.put("user", user);
    			        props.put("password", pwd);
    			        this.conn=DriverManager.getConnection(url,props);
    			        //conn=DriverManager..getConnection(url,user,pwd);
    		}
    		
    		return conn;
     }
	
     
	public String [] getAllDataTableArray() {
	        

		List<String> tables = new ArrayList<String>();
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			//"TABLE", "VIEW", "SYSTEM   TABLE", "GLOBAL   TEMPORARY", "LOCAL   TEMPORARY", "ALIAS", "SYNONYM"
			String[] types = { "TABLE" };
			ResultSet tabs = dbMetaData.getTables(null,null, null, types);
			//ResultSet tabs = dbMetaData.getTables(null,null, null, types);
			int i=0;
			while (tabs.next()) {
				if(((String)tabs.getObject("TABLE_NAME")).indexOf("$")!=-1)
					continue;
				tables.add(tabs.getObject("TABLE_NAME").toString().toUpperCase());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		String[] tb= new String[tables.size()];
		tables.toArray(tb);
		return tb; 

	}
	
	public List<ColumnInfo> getColumnInfoList(String tablename)
	{
		List<ColumnInfo> list = new ArrayList<ColumnInfo>();
		ColumnInfo columnInfo;
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			ResultSet colRet = dbMetaData.getColumns(null,null, tablename,"%");
			while(colRet.next()) {
				columnInfo = new ColumnInfo();
				columnInfo.setNameDb(colRet.getString("COLUMN_NAME"));
				columnInfo.setNameClassl(Tools.toFirstLowerCase(colRet.getString("COLUMN_NAME")));
				columnInfo.setNameClassu(Tools.toFirstUpperCase(colRet.getString("COLUMN_NAME")));
				columnInfo.setRemark(colRet.getString("REMARKS"));
				columnInfo.setNull(colRet.getInt("NULLABLE")==1?true:false);
				columnInfo.setType(colRet.getInt("DATA_TYPE"));
				columnInfo.setLength(colRet.getInt("COLUMN_SIZE"));
				list.add(columnInfo);
			}
		} catch (Exception e) {
			 e.printStackTrace();
		}
		return list;
	}

	
	
	
	
//	SELECT NULL AS table_cat,
//    t.owner AS table_schem,
//    t.table_name AS table_name,
//    t.column_name AS column_name,
//    DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,
//            'LONG', -1, 'DATE', 91, 'RAW', -3, 'LONG RAW', -4,  
//            'BLOB', 2004, 'CLOB', 2005, 'BFILE', -13, 'FLOAT', 6, 
//            'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', -101, 
//            'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, 
//            'INTERVAL YEAR(2) TO MONTH', -103, 
//            'INTERVAL DAY(2) TO SECOND(6)', -104, 
//            'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 
//            1111)
//           AS data_type,
//    t.data_type AS type_name,
//    DECODE (t.data_precision, null, t.data_length, t.data_precision)
//           AS column_size,
//    0 AS buffer_length,
//    t.data_scale AS decimal_digits,
//    10 AS num_prec_radix,
//    DECODE (t.nullable, 'N', 0, 1) AS nullable,
//    c.comments AS remarks,
//    t.data_default AS column_def,
//    0 AS sql_data_type,
//    0 AS sql_datetime_sub,
//    t.data_length AS char_octet_length,
//    t.column_id AS ordinal_position,
//    DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable
//FROM all_tab_columns t, all_col_comments c
//WHERE t.owner LIKE :1 ESCAPE '/'
//AND t.table_name LIKE :2 ESCAPE '/'
//AND t.column_name LIKE :3 ESCAPE '/'
//AND t.owner = c.owner (+)
//AND t.table_name = c.table_name (+)
//AND t.column_name = c.column_name (+)
//ORDER BY table_schem, table_name, ordinal_position

	  
	  
}
